using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.IO;
using System.Text;
using System.Reflection;
using Cobalt75.GreyMatter;
using GMID = System.Int64;
using FirebirdSql.Data.Isql;
using FirebirdSql.Data.FirebirdClient;



namespace Cobalt75.GreyMatter.FirebirdSql
{
    public class FirebirdSqlGMCommunicator : GMCommunicator
    {
        #region Constructors, Diposers & Initalisers
        string pPath;
        public string Path
        {
            get
            {
                return pPath;
            }

        }
        public FirebirdSqlGMCommunicator(string path, string database)
            : base(database)
        {
            pPath = path;
            Init("ServerType=1;Dialect=3;Database=" + path + database + ".fdb;user id=" + Database + ";password=" + Database + ";",false);
        }
        private void Init(string connectionString,bool CreateNew)
        {
            FbConnectionStringBuilder sB = new FbConnectionStringBuilder(connectionString);
            if (CreateNew)
                File.Delete(sB.Database);

            if (!File.Exists(sB.Database))
            {
                sB.Charset = "UTF8";
                FbConnection.CreateDatabase(sB.ToString());
            }
            Connection = new FbConnection();
            
            Connection.ConnectionString = connectionString;
            

            Connection.Open();
        }
        ~FirebirdSqlGMCommunicator()
        {
            Dispose(true);
        }
        #endregion
        #region GMQuery To Firebird SQL commands

        public override char QuoteChar
        {
            get
            {
                return '"';
            }
        }
        public override char ParameterChar
        {
            get
            {
                return '@';
            }
        }
        protected override string ToString(GMQueryClause clause)
        {
            switch (clause.ClauseOperator)
            {
                case GMQueryClauseOperator.Equals:
                    return (ToString(clause.ItemA) + " = " + ToString(clause.ItemB));
                case GMQueryClauseOperator.GreaterThan:
                    return (ToString(clause.ItemA) + " > " + ToString(clause.ItemB));
                case GMQueryClauseOperator.IsNot:
                    return (ToString(clause.ItemA) + " != " + ToString(clause.ItemB));
                case GMQueryClauseOperator.LessThan:
                    return (ToString(clause.ItemA) + " < " + ToString(clause.ItemB));
                case GMQueryClauseOperator.Like:
                    return (ToString(clause.ItemA) + " LIKE " + ToString(clause.ItemB));
                case GMQueryClauseOperator.IsNotNull:
                    return (ToString(clause.ItemA) + " is not NULL");
                case GMQueryClauseOperator.IsNull:
                    return (ToString(clause.ItemA) + " is NULL");
                case GMQueryClauseOperator.FindInSet:
                    return ("FIND_IN_SET(" + ToString(clause.ItemA) + "," + ToString(clause.ItemB) + ")");
                case GMQueryClauseOperator.OtherFunction:
                    return (String.Format(CultureInfo.InvariantCulture,clause.OtherFunctionFormat, ToString(clause.ItemA), ToString(clause.ItemB)));

                default:
                    return "??";
            }
        }


        public override string GetFunctionFormatString(GMQuerySqlFunction function, params GMQueryClauseItemReference[] references)
        {
            switch (function)
            {
                case GMQuerySqlFunction.StringConcatenate:
                    return(String.Join("||",Array.ConvertAll<GMQueryClauseItemReference,string>(references,new Converter<GMQueryClauseItemReference,string>(ToString))));


                default:
                    return ("?");
            }
        }

        public override IDbCommand CommandFromQuery(GMQuery query)
        {
            FbCommand com = new FbCommand();
            SetCommandFromQuery(query, com);
            return (com);
        }
        #endregion

        #region Other Firebird Specific overrides
        public override IDataParameter CreateParameter(string paraName, object paraValue)
        {
            return new FbParameter(ParameterNameFromId(paraName), paraValue);

        }
        protected override GMID GetLastInsertId(IDbCommand command, string tableName)
        {
            command.CommandText = String.Format(CultureInfo.InvariantCulture,"select GEN_ID(\"GEN_{0}_ID\",0) FROM RDB$DATABASE;",tableName);

            return (GMID)Convert.ChangeType(command.ExecuteScalar().ToString(), typeof(GMID), CultureInfo.InvariantCulture);
        }
        protected override object DBToClr(object obj, GMColumnBaseAttribute attr)
        {
            if (attr == null)
            {
                return obj; //For ID lookup
            }
            object v = obj;
            Type t = attr.ClrType;
            if (attr.IsPrimary)
            {
                return v;
            }
            if (attr.ClrType.IsGenericType)
            {

                Type ta = attr.ClrType.GetGenericTypeDefinition();
                Type tb = typeof(GMRelation<>);

                if (ta == tb && v == DBNull.Value)
                {
                    return ((GMID)0);
                }
                return v;

            }
            if (t.IsArray && t.IsValueType)
            {
                return v;
            }
            if (t.IsValueType)
            {
                if (t == typeof(GMID))
                {
                    return v;
                }
                if (t == typeof(Int16))
                {

                    return v;
                }
                if (t == typeof(Int32))
                {

                    return v;
                }
                if (t == typeof(Int64))
                {

                    return v;
                }
                if (t == typeof(UInt16))
                {

                    return v;
                }
                if (t == typeof(UInt32))
                {

                    return v;
                }
                if (t == typeof(UInt64))
                {

                    return v;
                }
                if (t == typeof(DateTime) && attr.Precision)
                {

                    return new DateTime((long)v);
                }
                if (t == typeof(DateTime) && !attr.Precision)
                {

                    return v;
                }
                if (t == typeof(char))
                {

                    return v;
                }
                if (t == typeof(decimal))
                {

                    return v;
                }
                if (t == typeof(bool) && v == typeof(SByte))
                {
                    return (((SByte)v) == 1);
                }
                if (t == typeof(bool))
                {

                    return v;
                }
                if (t == typeof(Byte))
                {

                    return v;
                }
                if (t == typeof(float))
                {

                    return v;
                }
                if (t == typeof(double))
                {

                    return v;
                }
            }
            if (t == typeof(string) || t.IsSubclassOf(typeof(string)))
            {
                if (attr.LengthType == GMColumnLength.Small)
                {

                    return v;
                }
                if (attr.LengthType == GMColumnLength.Medium)
                {

                    return v;
                }
                if (attr.LengthType == GMColumnLength.Large)
                {

                    return v;
                }
                if (attr.LengthType == GMColumnLength.Undefined)
                {

                    return v;
                }
            }
            return (v);
        }
        protected override object ClrToDB(object obj, GMColumnBaseAttribute attr)
        {
            object v = attr.ClrField.GetValue(obj);
            Type t = attr.ClrType;
            if (attr.IsPrimary)
            {
                return v;
            }
            else if (attr.ClrType.IsGenericType)
            {
                Type ta = attr.ClrType.GetGenericTypeDefinition();
                Type tb = typeof(GMRelation<>);

                if (ta == tb)
                {
                    IGMRelation igm = (IGMRelation)v;
                    if (igm.Id == 0)
                    {
                        return (null);
                    }
                    //return (((GMRelation<GMTable>)v).Value.ID);
                    return igm.Id;
                }
                return v;

            }
            else if (t.IsArray && t.IsValueType)
            {
                return v;
            }
            else if (t.IsValueType)
            {
                if (t == typeof(GMID))
                {
                    return v;
                }
                else if (t == typeof(Int16))
                {

                    return v;
                }
                else if (t == typeof(Int32))
                {

                    return v;
                }
                else if (t == typeof(Int64))
                {

                    return v;
                }
                else if (t == typeof(UInt16))
                {

                    return v;
                }
                else if (t == typeof(UInt32))
                {

                    return v;
                }
                else if (t == typeof(UInt64))
                {

                    return v;
                }
                else if (t == typeof(DateTime) && attr.Precision)
                {

                    return ((DateTime)v).Ticks;
                }
                else if (t == typeof(DateTime) && !attr.Precision)
                {

                    return v;
                }
                else if (t == typeof(char))
                {

                    return v;
                }
                else if (t == typeof(decimal))
                {

                    return v;
                }
                else if (t == typeof(bool))
                {

                    return v;
                }
                else if (t == typeof(Byte))
                {

                    return v;
                }
                else if (t == typeof(float))
                {

                    return v;
                }
                else if (t == typeof(double))
                {

                    return v;
                }
            }
            else if (t == typeof(string) || t.IsSubclassOf(typeof(string)))
            {
                if (attr.LengthType == GMColumnLength.Small)
                {

                    return v;
                }
                else if (attr.LengthType == GMColumnLength.Medium)
                {

                    return v;
                }
                else if (attr.LengthType == GMColumnLength.Large)
                {

                    return v;
                }
                else if (attr.LengthType == GMColumnLength.Undefined)
                {

                    return v;
                }
                else
                {
                    return v;
                }
            }
            return (v);
        }
        protected override void SetDatabaseSchemaField(Type type, ref GMColumnBaseAttribute attribute)
        {
            if (attribute.IsPrimary)
            {
                attribute.SetDatabase("bigint", "0");
                return;
            }
            else if (attribute.ClrType.IsGenericType)
            {
                Type ta = attribute.ClrType.GetGenericTypeDefinition();
                Type tb = typeof(GMRelation<>);

                if (ta == tb)
                {
                    attribute.SetDatabase("bigint", "0");
                    return;
                }
                attribute.SetDatabase("?", "?");
                return;

            }
            else if (type.IsArray && type.IsValueType)
            {
                attribute.SetDatabase("?", "?");
                return;
            }
            else if (type.IsValueType)
            {
                if (type == typeof(GMID))
                {
                    attribute.SetDatabase("bigint", "0");
                    return;
                }
                else if (type == typeof(Int16))
                {

                    attribute.SetDatabase("SMALLINT", "0");
                    return;
                }
                else if (type == typeof(Int32))
                {

                    attribute.SetDatabase("INT", "0");
                    return;
                }
                else if (type == typeof(Int64))
                {

                    attribute.SetDatabase("bigint", "0");
                    return;
                }
                else if (type == typeof(UInt16))
                {

                    attribute.SetDatabase("SMALLINT", "0");
                    return;
                }
                else if (type == typeof(UInt32))
                {

                    attribute.SetDatabase("INT", "0");
                    return;
                }
                else if (type == typeof(UInt64))
                {

                    attribute.SetDatabase("bigint", "0");
                    return;
                }
                else if (type == typeof(DateTime) && attribute.Precision)
                {

                    attribute.SetDatabase("bigint", "0");
                    return;
                }
                else if (type == typeof(DateTime) && !attribute.Precision)
                {

                    attribute.SetDatabase("DATETIME", "0");
                    return;
                }
                else if (type == typeof(char))
                {

                    attribute.SetDatabase("BLOB SUB_TYPE TEXT", "");
                    return;
                }
                else if (type == typeof(decimal))
                {

                    attribute.SetDatabase("DECIMAL(65,30)", "0");
                    return;
                }
                else if (type == typeof(bool))
                {

                    attribute.SetDatabase("TINYINT(1)", "0");
                    return;
                }
                else if (type == typeof(Byte))
                {

                    attribute.SetDatabase("TINYINT", "0");
                    return;
                }
                else if (type == typeof(float))
                {

                    attribute.SetDatabase("FLOAT", "0");
                    return;
                }
                else if (type == typeof(double))
                {

                    attribute.SetDatabase("DOUBLE", "0");
                    return;
                }
            }
            else if (type == typeof(string) || type.IsSubclassOf(typeof(string)))
            {
                if (attribute.LengthType == GMColumnLength.Small)
                {

                    attribute.SetDatabase("BLOB SUB_TYPE TEXT", null);
                    return;
                }
                else if (attribute.LengthType == GMColumnLength.Medium)
                {

                    attribute.SetDatabase("BLOB SUB_TYPE TEXT", null);
                    return;
                }
                else if (attribute.LengthType == GMColumnLength.Large)
                {

                    attribute.SetDatabase("BLOB SUB_TYPE TEXT", null);
                    return;
                }
                else if (attribute.LengthType == GMColumnLength.Undefined)
                {

                    attribute.SetDatabase("BLOB SUB_TYPE TEXT", null);
                    return;
                }
                else
                {
                    attribute.SetDatabase("VARCHAR(" + attribute.ColumnLength + ")", "");

                    return;
                }
            }
            attribute.SetDatabase("BLOB", "");
            return;
        }
        #endregion
        #region DDL Sql
        public override string TempIdTableCreationSql()
        {
            return (String.Format(CultureInfo.InvariantCulture, @"
DROP TABLE IF EXISTS ""tbl_ids"";
CREATE TABLE ""tbl_ids"" (
  ""col"" bigint NOT NULL default '0'
);
"));
        }
        public override string CreateTableSql(GMLinkTable linkTableAttr)
        {
            
            StringBuilder sB = new StringBuilder();
            sB.AppendLine(String.Format(CultureInfo.InvariantCulture,"CREATE TABLE \"{0}\" (",
                linkTableAttr.Name));
            List<string> CreateLines = new List<string>();


            CreateLines.Add(String.Format(CultureInfo.InvariantCulture,"\"{0}_id\" bigint NOT NULL",
                linkTableAttr.Master.TableName
                ));

            CreateLines.Add(String.Format(CultureInfo.InvariantCulture,"\"{0}_id\" bigint NOT NULL",
                linkTableAttr.Relation.TableName));

            if (linkTableAttr.Index != null)
            {
                CreateLines.Add(String.Format(CultureInfo.InvariantCulture,"\"{0}_id\" bigint NOT NULL",
                    linkTableAttr.Index.TableName));
            }
            if (linkTableAttr.Parameter != null)
            {
                CreateLines.Add(String.Format(CultureInfo.InvariantCulture,"\"{0}\" {1}",
                    linkTableAttr.Parameter.ColumnName,
                    linkTableAttr.Parameter.DatabaseTypeId));

            }
            //CreateLines.Add(String.Format());
            if (linkTableAttr.Unique && linkTableAttr.Index != null) //Only one relation per index.
            {
                CreateLines.Add(String.Format(CultureInfo.InvariantCulture,"PRIMARY KEY (\"{0}_id\",\"{1}_id\")",
                    linkTableAttr.Master.TableName,
                    linkTableAttr.Index.TableName));

            }
            else if (!linkTableAttr.Duplicates) //More than one of the same relation with the same index.
            {
                if (linkTableAttr.Index != null)
                {
                    CreateLines.Add(String.Format(CultureInfo.InvariantCulture,"PRIMARY KEY (\"{0}_id\",\"{1}_id\",\"{2}_id\")",
                        linkTableAttr.Master.TableName,
                        linkTableAttr.Relation.TableName,
                        linkTableAttr.Index.TableName));
                }
                else
                {
                    CreateLines.Add(String.Format(CultureInfo.InvariantCulture,"PRIMARY KEY (\"{0}_id\",\"{1}_id\")", linkTableAttr.Master.TableName,
                        linkTableAttr.Relation.TableName));

                }
            }
            sB.AppendLine(String.Join("," + Environment.NewLine, CreateLines.ToArray()));
            sB.AppendLine(");");
            /*
            string fkeylinktblname = ltbl.Name;
            if (fkeylinktblname.Length > 32)
            {
                fkeylinktblname = "";
                foreach (string str in ltbl.Name.Split('_'))
                {
                    fkeylinktblname += str.Substring(0, Math.Min(3, str.Length));

                }
            }

            StringBuilder ConstraintBuilder = new StringBuilder();

            ConstraintBuilder.Length = 0;
            ConstraintBuilder.AppendFormat("CONSTRAINT {0}_fk_{1}",
                fkeylinktblname,
                ltbl.Master.TableName);
            ConstraintBuilder.AppendLine();
            ConstraintBuilder.AppendFormat("   FOREIGN KEY ({0}_id) REFERENCES {0} (id)",
                ltbl.Master.TableName);
            ConstraintBuilder.AppendLine();
            ConstraintBuilder.AppendLine("      ON UPDATE CASCADE");
            ConstraintBuilder.AppendLine("      ON DELETE RESTRICT");
            CreateLines.Add(ConstraintBuilder.ToString());
            ConstraintBuilder.Length = 0;


            ConstraintBuilder.AppendFormat("CONSTRAINT {0}_fk_{1}",
                fkeylinktblname,
                ltbl.Relation.TableName);
            ConstraintBuilder.AppendLine();
            ConstraintBuilder.AppendFormat("   FOREIGN KEY ({0}_id) REFERENCES {0} (id)",
                ltbl.Relation.TableName);
            ConstraintBuilder.AppendLine();
            ConstraintBuilder.AppendLine("      ON UPDATE CASCADE");
            ConstraintBuilder.AppendLine("      ON DELETE RESTRICT");
            CreateLines.Add(ConstraintBuilder.ToString());

            if (ltbl.Index != null)
            {

                ConstraintBuilder.Length = 0;


                ConstraintBuilder.AppendFormat("CONSTRAINT {0}_fk_{1}",
                    fkeylinktblname,
                    ltbl.Index.TableName);
                ConstraintBuilder.AppendLine();
                ConstraintBuilder.AppendFormat("   FOREIGN KEY ({0}_id) REFERENCES {0} (id)",
                    ltbl.Index.TableName);
                ConstraintBuilder.AppendLine();
                ConstraintBuilder.AppendLine("      ON UPDATE CASCADE");
                ConstraintBuilder.AppendLine("      ON DELETE RESTRICT");
                CreateLines.Add(ConstraintBuilder.ToString());
            }


            sB.AppendLine(");");
            */
            return sB.ToString();

        }
        static string[] CreateTableIndexes(GMTableAttribute tableAttr)
        {

            List<string> IndicesSQL = new List<string>();
            string unique;
            foreach (KeyValuePair<FieldInfo, GMColumnBaseAttribute> kp in tableAttr.Columns)
            {
                if (kp.Value.Key)
                {
                    if (kp.Value.Unique)
                    {
                        unique = "UNIQUE";
                    }
                    else
                    {

                        unique = "";
                    }
                    IndicesSQL.Add(
                        String.Format(CultureInfo.InvariantCulture,"CREATE {0} INDEX \"key_{1}_{2}\" ON \"{1}\"(\"{2}\");",
                           unique,
                            tableAttr.TableName,
                            kp.Value.ColumnName
                        )
                        );

                }
            }
            return IndicesSQL.ToArray();
        }
        static string[] CreateTableIndexes(GMLinkTable linkTableAttr)
        {

            List<string> IndicesSQL = new List<string>();
            IndicesSQL.Add(
            String.Format(CultureInfo.InvariantCulture,"CREATE INDEX \"k{0}{1}\" ON \"{0}\"(\"{2}_id\");",

                linkTableAttr.Name,
                linkTableAttr.Master.TableId,
                linkTableAttr.Master.TableName
                ));
            IndicesSQL.Add(
            String.Format(CultureInfo.InvariantCulture,"CREATE INDEX \"k{0}{1}\" ON \"{0}\"(\"{2}_id\");",

                linkTableAttr.Name,
                linkTableAttr.Relation.TableId,
                linkTableAttr.Relation.TableName
                ));

            if (linkTableAttr.Index != null)
            {

                IndicesSQL.Add(
                String.Format(CultureInfo.InvariantCulture,"CREATE INDEX \"k{0}{1}_id\" ON \"{0}\"(\"{2}_id\");",

                    linkTableAttr.Name,
                    linkTableAttr.Index.TableId,
                    linkTableAttr.Index.TableName
                    ));
            }



            return IndicesSQL.ToArray();
        }
        static string[] CreateTableForeignKeys(GMTableAttribute tableAttr)
        {

            List<string> ConstraintsSQL = new List<string>();




            StringBuilder ConstraintBuilder = new StringBuilder();
            foreach (KeyValuePair<FieldInfo, GMColumnBaseAttribute> kp in tableAttr.Columns)
            {
                if (kp.Value.GMRelationType != null && kp.Value.IsDatabaseColumn)
                {
                    ConstraintBuilder.Length = 0;
                    ConstraintBuilder.AppendFormat("ALTER TABLE \"{0}\" ADD CONSTRAINT \"{3}{1}_fk_{2}\"",
                        tableAttr.TableName,
                        tableAttr.TableId,
                        kp.Value.GMRelationAttr.TableId,
                        kp.Value.ColumnPrefix);
                    ConstraintBuilder.AppendLine();
                    ConstraintBuilder.AppendFormat("   FOREIGN KEY (\"{0}\") REFERENCES \"{1}\" (\"id\")",
                        kp.Value.ColumnName, kp.Value.GMRelationAttr.TableName);

                    

                    ConstraintBuilder.AppendLine();
                    ConstraintBuilder.AppendLine("      ON UPDATE CASCADE");
                    //ConstraintBuilder.AppendLine("      ON DELETE RESTRICT");
                    ConstraintBuilder.AppendLine(";");

                    ConstraintsSQL.Add(ConstraintBuilder.ToString());
                }
            }
            return ConstraintsSQL.ToArray();
        }
        static string[] CreateTableForeignKeys(GMLinkTable linkTableAttr)
        {

            string fkeylinktblname = linkTableAttr.Name;
            if (fkeylinktblname.Length > 32)
            {
                fkeylinktblname = "";
                foreach (string str in linkTableAttr.Name.Split('_'))
                {
                    fkeylinktblname += str.Substring(0, Math.Min(3, str.Length));

                }
            }
            List<string> ConstraintsSQL = new List<string>();


            StringBuilder ConstraintBuilder = new StringBuilder();

            ConstraintBuilder.Length = 0;
            ConstraintBuilder.AppendFormat("ALTER TABLE \"{0}\" ADD CONSTRAINT \"{0}_fk_{1}\"",
                fkeylinktblname,
                linkTableAttr.Master.TableId);
            ConstraintBuilder.AppendLine();
            ConstraintBuilder.AppendFormat("   FOREIGN KEY (\"{0}_id\") REFERENCES \"{0}\" (\"id\")",
                linkTableAttr.Master.TableName);

            ConstraintBuilder.AppendLine();
            ConstraintBuilder.AppendLine("      ON UPDATE CASCADE");
            //ConstraintBuilder.AppendLine("      ON DELETE RESTRICT");
            ConstraintBuilder.AppendLine(";");
            ConstraintsSQL.Add(ConstraintBuilder.ToString());

            ConstraintBuilder.Length = 0;


            ConstraintBuilder.AppendFormat("ALTER TABLE \"{0}\" ADD CONSTRAINT \"{0}_fk_{1}\"",
                fkeylinktblname,
                linkTableAttr.Relation.TableId);
            ConstraintBuilder.AppendLine();
            ConstraintBuilder.AppendFormat("   FOREIGN KEY (\"{0}_id\") REFERENCES \"{0}\" (\"id\")",
                linkTableAttr.Relation.TableName);

            ConstraintBuilder.AppendLine();
            ConstraintBuilder.AppendLine("      ON UPDATE CASCADE");
            //ConstraintBuilder.AppendLine("      ON DELETE RESTRICT");
            ConstraintBuilder.AppendLine(";");
            ConstraintsSQL.Add(ConstraintBuilder.ToString());

            if (linkTableAttr.Index != null)
            {

                ConstraintBuilder.Length = 0;


                ConstraintBuilder.AppendFormat("ALTER TABLE \"{0}\" ADD CONSTRAINT \"{0}_fk_{1}\"",
                    fkeylinktblname,
                    linkTableAttr.Index.TableId);
                ConstraintBuilder.AppendLine();
                ConstraintBuilder.AppendFormat("   FOREIGN KEY (\"{0}_id\") REFERENCES \"{0}\" (\"id\")",
                    linkTableAttr.Index.TableName);

                ConstraintBuilder.AppendLine();
                ConstraintBuilder.AppendLine("      ON UPDATE CASCADE");
                //ConstraintBuilder.AppendLine("      ON DELETE RESTRICT");
                ConstraintBuilder.AppendLine(";");
                ConstraintsSQL.Add(ConstraintBuilder.ToString());
            }



            return ConstraintsSQL.ToArray();
        }
        public override string CreateTableSql(GMTableAttribute tableAttr)
        {
            StringBuilder sB = new StringBuilder();
            sB.AppendLine(String.Format(CultureInfo.InvariantCulture,"CREATE TABLE  \"{0}\" (",
                tableAttr.TableName));
            List<string> CreateLines = new List<string>();
            string nullDef;
            string autoIncrementDef;
            string defaultDef;
            foreach (KeyValuePair<FieldInfo, GMColumnBaseAttribute> kp in tableAttr.Columns)
            {
                if (kp.Value.IsDatabaseColumn)
                {
                    if (kp.Value.AllowNull)
                    {
                        nullDef = "";
                    }
                    else
                    {
                        nullDef = "NOT NULL";
                    }
                    if (kp.Value.IsPrimary)
                    {
                        autoIncrementDef = "";
                        //autoIncrementDef = "AUTO_INCREMENT";
                        defaultDef = "";
                    }
                    else
                    {
                        autoIncrementDef = "";
                        defaultDef = "";
                    }


                    CreateLines.Add(
                        String.Format(CultureInfo.InvariantCulture,"   \"{0}\" {1} {2} {3} {4}",
                            kp.Value.ColumnName,
                            kp.Value.DatabaseTypeId,
                            nullDef,
                            autoIncrementDef,
                            defaultDef
                        )
                        );
                }
            }
            CreateLines.Add("PRIMARY KEY (\"id\")");

            sB.AppendLine(String.Join("," + Environment.NewLine, CreateLines.ToArray()));
            sB.AppendLine(");");

 


  
            


            return sB.ToString();

        }
        static string CreateSequenceSql(GMTableAttribute tableAttr)
        {

            StringBuilder sB = new StringBuilder();

            sB.AppendFormat("CREATE SEQUENCE \"GEN_{0}_ID\";",
                tableAttr.TableName);

            return (sB.ToString());

        }
        static string CreateTriggerSql(GMTableAttribute tableAttr)
        {

            StringBuilder sB = new StringBuilder();

            sB.AppendFormat("CREATE TRIGGER \"ID_TRIG_{0}\" FOR \"{0}\" ",
                tableAttr.TableName);
            sB.AppendLine();
            sB.AppendLine("BEFORE INSERT");
            sB.AppendLine("AS");
            sB.AppendLine("BEGIN ");
            sB.AppendLine("  IF (NEW.\"id\" IS NULL) ");
            sB.AppendFormat("    THEN NEW.\"id\" = GEN_ID(\"GEN_{0}_ID\", 1); ",
                tableAttr.TableName);
            sB.AppendLine();
            sB.AppendLine("END ;");

            return (sB.ToString());

        }
        static string TableSyncSql()
        {
            return String.Format(CultureInfo.InvariantCulture, @"
CREATE TABLE ""tablesync"" (
  ""tablename"" varchar(255) NOT NULL,
  ""modifiedticks"" bigint NOT NULL,
  PRIMARY KEY  (""tablename"")
);

");
        }
        public override void CreateDBSchema()
        {
            Console.WriteLine("DB Schema (designed for Firebird 1.5+)");
            string conString = Connection.ConnectionString;
            Connection.Close();
            Connection.Dispose();
            FbConnection.ClearAllPools();
            FbConnection.DropDatabase(conString);
            Init(conString, true);

            //First create the main tables
            //(In order of priority)
            SortedDictionary<int, GMTableAttribute> SORTtabAttrs = new SortedDictionary<int, GMTableAttribute>();

            foreach (KeyValuePair<Type, GMTableAttribute> kp in ReflectionCache)
            {
                SORTtabAttrs.Add(kp.Value.Priority, kp.Value);
            }

            IDbCommand command = Connection.CreateCommand();

            



            command.CommandText = TableSyncSql();
            Console.WriteLine(command.CommandText);
            command.ExecuteNonQuery();

            foreach (GMTableAttribute tableAttr in SORTtabAttrs.Values)
            {

                command.CommandText = String.Format(CultureInfo.InvariantCulture,"INSERT INTO \"tablesync\" VALUES ('{0}','0');",
                    tableAttr.TableName);
                Console.WriteLine(command.CommandText);
                command.ExecuteNonQuery();

                command.CommandText = CreateTableSql(tableAttr);
                Console.WriteLine(command.CommandText);
                command.ExecuteNonQuery();

                command.CommandText = CreateSequenceSql(tableAttr);
                Console.WriteLine(command.CommandText);
                command.ExecuteNonQuery();

                command.CommandText = CreateTriggerSql(tableAttr);
                Console.WriteLine(command.CommandText);
                command.ExecuteNonQuery();


            }
            //Then create the link tables

            foreach (GMLinkTable ltbl in LinkTables)
            {
                command.CommandText = String.Format(CultureInfo.InvariantCulture,"INSERT INTO \"tablesync\" VALUES ('{0}','0');",
                    ltbl.Name);
                Console.WriteLine(command.CommandText);
                command.ExecuteNonQuery();

                command.CommandText = CreateTableSql(ltbl);
                Console.WriteLine(command.CommandText);
                command.ExecuteNonQuery();
            }
            string[] commands;
            //Create indicies
            foreach (GMTableAttribute tableAttr in SORTtabAttrs.Values)
            {
                commands = CreateTableIndexes(tableAttr);
                foreach (string comstr in commands)
                {
                    command.CommandText = comstr;
                    Console.WriteLine(command.CommandText);
                    command.ExecuteNonQuery();
                }
            }
            foreach (GMLinkTable ltbl in LinkTables)
            {
                commands = CreateTableIndexes(ltbl);
                foreach (string comstr in commands)
                {
                    command.CommandText = comstr;
                    Console.WriteLine(command.CommandText);
                    command.ExecuteNonQuery();
                }
            }
            //Forigen Keys
            foreach (GMTableAttribute tableAttr in SORTtabAttrs.Values)
            {
                commands = CreateTableForeignKeys(tableAttr);
                foreach (string comstr in commands)
                {
                    command.CommandText = comstr;
                    Console.WriteLine(command.CommandText);
                    command.ExecuteNonQuery();
                }
            }
            //Then create the link tables

            foreach (GMLinkTable ltbl in LinkTables)
            {

                commands = CreateTableForeignKeys(ltbl);
                foreach (string comstr in commands)
                {
                    command.CommandText = comstr;
                    Console.WriteLine(command.CommandText);
                    command.ExecuteNonQuery();
                }
            }




            Console.WriteLine("Create DB committed");



        }
        #endregion
    }
}
